﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Drawing;

namespace ExcelFileQueryTool
{
    public class ClientFileConclusionProcessor
    {
        private string SessionId = DateTime.Now.ToString();
        public bool IsSupport(string input)
        {
            return Path.GetExtension(input).ToLower() == ".xlsx";
        }

        public ClientData Process(string input)
        {
            var client = new ClientData();
            client.Name = Path.GetFileNameWithoutExtension(input).Replace("[对数表]", string.Empty); // keep original filename,no mapping

            #region Process logic

            try
            {
                using (var package = new ExcelPackage(new FileInfo(input)))
                {
                    client.UnpaidAmount = 0m;
                    client.ClientType = ClientFileService.GetClientTypeFromFile(input);

                    var book = package.Workbook;

                    var conclusionSheets = new List<ExcelWorksheet>();
                    var detailSheets = new List<ExcelWorksheet>();

                    foreach (var sheet in book.Worksheets)
                    {
                        if (sheet.IsHistorySheet())
                            continue;

                        if (sheet.IsDataSheet() == false)
                            continue;

                        if (sheet.IsConclusionSheet())
                            conclusionSheets.Add(sheet);
                        else
                            detailSheets.Add(sheet);
                    }

                    if (conclusionSheets.Count > 0)
                    {
                        client.TradeParts = new List<ClientTradePartData>();

                        foreach (var sheet in conclusionSheets)
                        {
                            ProcessMaterialTradeConclusionSheet(client, sheet);
                        }

                        if (client.UnpaidAmount != 0 && detailSheets.Count > 0) // improve processing speed
                        {
                            foreach (var sheet in detailSheets)
                            {
                                GetLastTradeDate(client, sheet);
                            }
                        }
                    }
                    else
                    {
                        foreach (var sheet in detailSheets)
                        {
                            ProcessMaterialTradeDetailSheet(client, sheet);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                client.UnpaidAmount = -999999999; // error value
                                                  //SimpleLogger.Log(new string[] { input, ex.Message });
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                DispatchSessionId = SessionId,
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Warn,
                                Title = "客户文件汇总信息生成操作[" + input + "]",
                                Content = ex.Message
                            });
            }

            #endregion

            return client;
        }

        private void ProcessMaterialTradeConclusionSheet(ClientData client, ExcelWorksheet sheet)
        {
            if (sheet.PrinterSettings.PrintArea == null)
                throw new Exception(string.Format("{0}->{1}的汇总表缺少打印区域，无法判断汇总表版本", client.Name, sheet.Name));

            RetriveContactInfo(client, sheet);

            IList<DateTime> tradeDateList;

            switch (sheet.PrinterSettings.PrintArea.Columns)
            {
                case 5:
                    {
                        ProcessMaterialTradeConclusionSheetV1(client, sheet, out tradeDateList);
                        break;
                    }
                case 9:
                    {
                        ProcessMaterialTradeConclusionSheetV2(client, sheet, out tradeDateList);
                        break;
                    }
                default:
                    {
                        throw new Exception(string.Format("{0}->{1}不受支持的汇总表", client.Name, sheet.Name));
                    }
            }

            if (tradeDateList != null && tradeDateList.Count > 0)
                SetLastTradeDate(client, tradeDateList.Max());
        }

        private void ProcessMaterialTradeConclusionSheetV1(ClientData client, ExcelWorksheet sheet, out IList<DateTime> tradeDateList)
        {
            tradeDateList = null;

            var curSheetUnpaid = sheet.Cells[sheet.Dimension.End.Row, 4].GetValue<decimal>();
            if (curSheetUnpaid == 0) // improve processing speed
                return;

            client.UnpaidAmount += curSheetUnpaid;

            int curRowIndex = 1;
            if (sheet.PrinterSettings.PrintArea != null)
                curRowIndex = sheet.PrinterSettings.PrintArea.Start.Row;

            curRowIndex++; // skip header row
            tradeDateList = new List<DateTime>();

            do
            {
                var typeVal = string.Empty;
                var namePrefix = string.Empty;

                for (; curRowIndex < sheet.Dimension.End.Row; curRowIndex++)
                {
                    var typeCell = sheet.Cells[curRowIndex, 1];
                    var tmpTypeVal = typeCell.GetValue<string>();

                    if (string.IsNullOrEmpty(tmpTypeVal) == false && tmpTypeVal.Contains("：") == false)
                    {
                        typeVal = tmpTypeVal.Replace(" ", string.Empty);

                        if (typeVal.Contains("货款"))
                            namePrefix = typeVal.Replace("货款", string.Empty);
                    }

                    // skip invalid rows,such as total row
                    if (sheet.Cells[curRowIndex, 2].Merge)
                        continue;

                    // skip empty row
                    if (string.IsNullOrEmpty(sheet.Cells[curRowIndex, 2].GetValue<string>()))
                        continue;

                    // skip clear-pay row
                    var total = sheet.Cells[curRowIndex, 4].GetValue<decimal>();
                    if (total == 0)
                        continue;

                    var refSheetName = sheet.Cells[curRowIndex, 4].Formula;
                    var refClient = new ClientData();

                    if (string.IsNullOrEmpty(refSheetName) == false)
                    {
                        var beginIndex = 0;
                        var endIndex = 0;
                        if (refSheetName.StartsWith("'"))
                            beginIndex = 1;

                        if (beginIndex == 0)
                            endIndex = refSheetName.IndexOf("!");
                        else
                            endIndex = refSheetName.IndexOf("'!");

                        refSheetName = refSheetName.Substring(beginIndex, endIndex - beginIndex);
                        var refSheet = sheet.Workbook.Worksheets[refSheetName];

                        RetriveContactInfo(refClient, refSheet, false);
                    }


                    var name = namePrefix + sheet.Cells[curRowIndex, 2].GetValue<string>();

                    if (typeVal == "来款")
                    {
                        name += "来款";
                        total *= -1;
                    }

                    DateTime tradeDate;
                    if (DateTime.TryParse(name, out tradeDate))
                        tradeDateList.Add(tradeDate);

                    client.TradeParts.Add(new ClientTradePartData
                    {
                        PartItem = name,
                        PartTotal = total,
                        PartPhone = refClient.Phone,
                        PartContact = refClient.ContactPerson,
                        PartAddress = refClient.Address
                    });
                }

            } while (curRowIndex < sheet.Dimension.End.Row);

        }

        private void ProcessMaterialTradeConclusionSheetV2(ClientData client, ExcelWorksheet sheet, out IList<DateTime> tradeDateList)
        {
            tradeDateList = null;

            var curSheetUnpaid = sheet.Cells[sheet.Dimension.End.Row, 3].GetValue<decimal>();
            if (curSheetUnpaid == 0) // improve processing speed
                return;

            client.UnpaidAmount += curSheetUnpaid;

            int curRowIndex = 1;
            if (sheet.PrinterSettings.PrintArea != null)
                curRowIndex = sheet.PrinterSettings.PrintArea.Start.Row;

            curRowIndex++; // skip header row
            tradeDateList = new List<DateTime>();

            var tradeParts = new List<ClientTradePartData>();

            for (; curRowIndex < sheet.Dimension.End.Row; curRowIndex++)
            {
                var firstCell = sheet.Cells[curRowIndex, 1];
                var firstCellVal = firstCell.GetValue<string>();

                if (string.IsNullOrEmpty(firstCellVal))
                    continue;

                if (firstCellVal == "日期")
                    continue;

                if (firstCell.Merge)
                {
                    if (firstCellVal.Contains("已结清"))
                    {
                        tradeDateList.Clear();
                        tradeParts.Clear();
                    }

                    continue;
                }

                var outTotalOfGoods = sheet.Cells[curRowIndex, 3].GetValue<decimal>();
                if (outTotalOfGoods != 0)
                    tradeParts.Add(new ClientTradePartData { PartItem = (firstCellVal + "出货"), PartTotal = outTotalOfGoods });

                var inTotalOfGoods = sheet.Cells[curRowIndex, 4].GetValue<decimal>();
                if (inTotalOfGoods != 0)
                    tradeParts.Add(new ClientTradePartData { PartItem = (firstCellVal + "进货"), PartTotal = (inTotalOfGoods * -1) });

                var inTotalOfMoney = sheet.Cells[curRowIndex, 5].GetValue<decimal>();
                if (inTotalOfMoney != 0)
                    tradeParts.Add(new ClientTradePartData { PartItem = (firstCellVal + "来款"), PartTotal = (inTotalOfMoney * -1) });

                var outTotalOfMoney = sheet.Cells[curRowIndex, 6].GetValue<decimal>();
                if (outTotalOfMoney != 0)
                    tradeParts.Add(new ClientTradePartData { PartItem = (firstCellVal + "去款"), PartTotal = outTotalOfMoney });

                //V2 doesn't need part contact info

                DateTime tradeDate;
                if (DateTime.TryParse(firstCellVal, out tradeDate))
                    tradeDateList.Add(tradeDate);
            }

            client.TradeParts = client.TradeParts.Concat(tradeParts).ToList();
        }

        private void ProcessMaterialTradeDetailSheet(ClientData client, ExcelWorksheet sheet)
        {
            RetriveContactInfo(client, sheet);

            client.UnpaidAmount += GetUnpaidTotal(sheet, client.Name);

            if (client.UnpaidAmount != 0)  // improve processing speed
                GetLastTradeDate(client, sheet);
        }

        private decimal GetUnpaidTotal(ExcelWorksheet sheet, string clientName)
        {
            const int MAX_TRY_ROWS = 3;

            var totalRowIndex = sheet.Dimension.End.Row;
            bool found = false;

            for (int i = MAX_TRY_ROWS; i > 0; i--)
            {
                if (ClientFileService.IsTotalRow(sheet, totalRowIndex))
                {
                    found = true;
                    break;
                }

                if (i > 1)
                    totalRowIndex--;
            }

            if (found == false)
                throw new Exception(string.Format("无法找到统计行，处理失败:{0}->{1}->{2}", clientName, sheet.Name, totalRowIndex.ToString()));

            return sheet.Cells[totalRowIndex, 8].GetValue<decimal>();
        }

        private void RetriveContactInfo(ClientData client, ExcelWorksheet sheet, bool enableLogging = true)
        {
            string contactInfo = sheet.HeaderFooter.OddHeader.LeftAlignedText;

            if (!string.IsNullOrEmpty(contactInfo))
            {
                var infoArr = ClientFileService.RetriveContactInfo(contactInfo);

                if (infoArr != null)
                {
                    client.ContactPerson = infoArr[0];
                    client.Phone = infoArr[1];
                    client.Address = infoArr[2];

                    if (enableLogging && string.IsNullOrEmpty(client.Phone))
                    {
                        MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                DispatchSessionId = SessionId,
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Warn,
                                Title = "客户文件汇总信息生成操作",
                                Content = client.Name + " 缺少联系人电话"
                            });
                        //SimpleLogger.Log("info>>>" + ");
                    }
                }
                else
                {
                    //SimpleLogger.Log("info>>>" + client.Name + " 匹配联系人信息失败");
                    if (enableLogging)
                    {
                        MessageDispatcherManager.DispatchMessage(
                                new AppMessage
                                {
                                    DispatchSessionId = SessionId,
                                    Time = DateTime.Now,
                                    Level = AppMessageLevel.Warn,
                                    Title = "客户文件汇总信息生成操作",
                                    Content = client.Name + " 匹配联系人信息失败"
                                });
                    }
                }
            }
            else
            {
                if (enableLogging)
                {
                    MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                DispatchSessionId = SessionId,
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Warn,
                                Title = "客户文件汇总信息生成操作",
                                Content = client.Name + " 缺少联系人信息"
                            });
                }
                //SimpleLogger.Log("info>>>" + client.Name + " 缺少联系人信息");
            }
        }

        private void GetLastTradeDate(ClientData client, ExcelWorksheet sheet)
        {
            try
            {
                string datePart = null;
                string yearPart = null;

                var curRowIndex = sheet.Dimension.End.Row;

                while (curRowIndex >= 1)
                {
                    var curRowFirstCell = sheet.Cells[curRowIndex, 1];
                    curRowIndex--;

                    // get date part
                    if (!curRowFirstCell.Merge && string.IsNullOrEmpty(datePart))
                    {
                        datePart = curRowFirstCell.Text;
                    }


                    // get year part
                    if (!string.IsNullOrEmpty(datePart) && curRowFirstCell.Merge)
                    {
                        var yearIdx = curRowFirstCell.Text.IndexOf("年");
                        if (yearIdx > 0) // 不可能为0
                        {
                            yearPart = curRowFirstCell.Text.Substring(0, yearIdx);
                            if (datePart.Contains("月份"))
                                datePart = datePart.Replace("月份", "") + "-1";
                            var date = DateTime.Parse(yearPart + "-" + datePart);
                            SetLastTradeDate(client, date);
                            break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //SimpleLogger.Log("info>>>" + client.Name + " 获取最后交易日期失败");
                MessageDispatcherManager.DispatchMessage(
                            new AppMessage
                            {
                                DispatchSessionId = SessionId,
                                Time = DateTime.Now,
                                Level = AppMessageLevel.Warn,
                                Title = "客户文件汇总信息生成操作",
                                Content = string.Format("{0}->{1}获取最后交易日期失败!" + Environment.NewLine + ex.ToString(), client.Name, sheet.Name)
                            });
            }
        }

        private static void SetLastTradeDate(ClientData client, DateTime date)
        {
            if (client.LastTradeDate.HasValue)
            {
                if (client.LastTradeDate.Value < date)
                    client.LastTradeDate = date;
            }
            else
            {
                client.LastTradeDate = date;
            }
        }
    }
}
